<template>
  <div>
    <!-- 不带样式报表导出 -->
    <el-card class="box-card">
      <el-button style="float: left" type="warning" size="small" @click="exportFn1">导出</el-button>
      <el-table
        :data="tableData"
        :span-method="arraySpanMethod"
        border
        style="width: 100%"
        id="el-table">
        <el-table-column label="不带样式报表导出" align="center">
          <el-table-column
            prop="id"
            label="ID"
            width="180">
          </el-table-column>
          <el-table-column
            prop="name"
            label="姓名">
          </el-table-column>
          <el-table-column
            prop="amount1"
            sortable
            label="数值 1">
          </el-table-column>
          <el-table-column
            prop="amount2"
            sortable
            label="数值 2">
          </el-table-column>
          <el-table-column
            prop="amount3"
            sortable
            label="数值 3">
          </el-table-column>
        </el-table-column>
      </el-table>
    </el-card>

    <!-- 带样式报表导出 -->
    <el-card class="box-card">
        <el-button style="float: left" type="warning" size="small" @click="setExport2Excel">导出</el-button>
      <el-table
        :data="tableData"
        :span-method="objectSpanMethod"
        border
        style="width: 100%; margin-top: 20px" id="mytable">
        <el-table-column label="带样式导出报表" align="center">
        <el-table-column
          prop="id"
          label="ID"
          width="180">
        </el-table-column>
        <el-table-column
          prop="name"
          label="姓名">
        </el-table-column>
        <el-table-column
          prop="amount1"
          label="数值 1（元）">
        </el-table-column>
        <el-table-column
          prop="amount2"
          label="数值 2（元）">
        </el-table-column>
        <el-table-column
          prop="amount3"
          label="数值 3（元）">
        </el-table-column>
        </el-table-column>

      </el-table>
    </el-card>
  </div>
</template>

<script>
import FileSaver from 'file-saver';
import XLSX from 'xlsx';
import XLSXSTYLE from 'xlsx-style';
  export default {
    data() {
      return {
        tableData: [{
          id: '12987122',
          name: '王小虎',
          amount1: '0.00',
          amount2: '3.2',
          amount3: 10
        }, {
          id: '12987123',
          name: '王小虎',
          amount1: '000000000165',
          amount2: '2021-02-01',
          amount3: 12
        }, {
          id: '12987124',
          name: '王小虎',
          amount1: '324',
          amount2: '1.9',
          amount3: 9
        }, {
          id: '12987125',
          name: '王小虎',
          amount1: '000000621',
          amount2: '2.2',
          amount3: 17
        }, {
          id: '12987126',
          name: '王小虎',
          amount1: '539',
          amount2: '4.1',
          amount3: 15
        }]
      };
    },
    methods: {
      arraySpanMethod({ row, column, rowIndex, columnIndex }) {
        if (rowIndex % 2 === 0) {
          if (columnIndex === 0) {
            return [1, 2];
          } else if (columnIndex === 1) {
            return [0, 0];
          }
        }
      },

      objectSpanMethod({ row, column, rowIndex, columnIndex }) {
        //表格合并单元格方法
        if (columnIndex === 0) {
          if (rowIndex % 2 === 0  && rowIndex == this.tableData.length-1) {
            return {
              rowspan: 1,
              colspan: 1
            };
          } if(rowIndex % 2==0){
            return {
              rowspan: 2,
              colspan: 1
            };
          }else {
            return {
              rowspan: 0,
              colspan: 0
            };
          }
        }
      },
      exportFn1(){
         let fix = document.querySelector('.el-table__fixed');
         let wb
         var xlsxParam = { raw: true }   //表格只进行解析 不做运算 可保留字符串中的0及日期格式 所见即所得
        //  使用el-table的filex属性时，会导致生成2份数据 原因：elementui 绘制了两份表格，其中一份隐藏
         if (fix) {
             wb = XLSX.utils.table_to_book(document.querySelector("#el-table",xlsxParam).removeChild(fix));
            document.querySelector("#el-table").appendChild(fix);
        } else {
             wb = XLSX.utils.table_to_book(document.querySelector("#el-table"),xlsxParam);
        }
        let wbout = XLSX.write(wb, {
            bookType: 'xlsx',
            bookSST: true,
            type: 'array'
        });

        try {
            FileSaver.saveAs(
                new Blob([wbout], { type: 'application/octet-stream' }),
                `商户日销售汇总表.xlsx` // 文件名
            );
        } catch (e) {
            if (typeof console !== 'undefined') {
                this.$message.error('导出失败');
                console.log(e, wbout);
            }
        }

        return wbout;
      },
      setExport2Excel() {
      /* generate workbook object from table */
      var xlsxParam = { raw: true }   //这个保证表格只进行解析 不做运算
      var wb = XLSX.utils.table_to_sheet(document.querySelector("#mytable"),xlsxParam);//mytable为表格的id名
      // if(!wb['!merges']){   //这个东西是当表格有合并的时候才会存在  并不能作为判断有无数据的标准
      //   this.$message.warning('无法导出：报表无数据');
      //   return
      // }
      for(var i = 0;i<11;i++){//每个单元格的宽度
        wb["!cols"][i]={wpx:100}
      }
      // 样式的文档地址
      // https://www.npmjs.com/package/xlsx-style
      for (const key in wb) {
        if(key.indexOf('!') === -1&&wb[key].v){
        wb[key].s ={
          font:{//字体设置
            sz:13,
            bold:false,
            color:{
              rgb:'000000'//十六进制，不带#
              }
          },
          alignment:{//文字对齐方式
            horizontal:'center',
            vertical:'center',
            wrap_text:true
          },
          border: { // 设置边框
            top: { style: 'thin' },
            bottom: { style: 'thin' },
            left: { style: 'thin' },
            right: { style: 'thin' }
          }
        }
        }
      }
      var data = this.addRangeBorder(wb['!merges'],wb) //合并项添加边框
      var filedata = this.sheet2blob(data)
      this.openDownloadDialog(filedata,"报表名称.xlsx")
    },
    //为合并项添加边框
    addRangeBorder(range,ws){
      let arr = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"];
      if(range){
        range.forEach(item=>{
          let startColNumber = Number(item.s.r), endColNumber = Number(item.e.r);
          let startRowNumber = Number(item.s.c), endRowNumber = Number(item.e.c);
          const test = ws[arr[startRowNumber] + (startColNumber + 1)];
          for(let col = startColNumber ; col <= endColNumber ; col++)
          {
            for(let row = startRowNumber; row <= endRowNumber ; row++)
            {
              ws[arr[row] + (col + 1)] = test;
            }
          }
        })
      }
      return ws;
    },
    //将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
    sheet2blob(sheet, sheetName) {
      sheetName = sheetName || 'sheet1';
      var workbook = {
        SheetNames: [sheetName],
        Sheets: {}
      };
      workbook.Sheets[sheetName] = sheet; // 生成excel的配置项
    
      var wopts = {
        bookType: 'xlsx', // 要生成的文件类型
        bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        type: 'binary'
      };
      var wbout = XLSXSTYLE.write(workbook, wopts);
      var blob = new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
      }); // 字符串转ArrayBuffer
      function s2ab(s) {
        var buf = new ArrayBuffer(s.length);
        var view = new Uint8Array(buf);
        for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
        return buf;
      }
      return blob;
    },
    openDownloadDialog(url, saveName) {
      if (typeof url == 'object' && url instanceof Blob) {
        url = URL.createObjectURL(url); // 创建blob地址
      }
      var aLink = document.createElement('a');
      aLink.href = url;
      aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
      var event;
      if (window.MouseEvent) event = new MouseEvent('click');
      else {
        event = document.createEvent('MouseEvents');
        event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
      }
      aLink.dispatchEvent(event);
      }
    }
  };
</script>